package com.jiangyg.mall.db;

import org.apache.commons.lang.StringUtils;
import org.hibernate.QueryException;
import org.hibernate.hql.internal.classic.ParserHelper;
import org.hibernate.internal.util.StringHelper;

import java.io.BufferedReader;
import java.io.Reader;
import java.sql.Clob;
import java.util.ArrayList;
import java.util.List;

/**
 * 工具描述：执行SQL
 *
 * @author jiangyg
 * @version v1.0
 */
public class SQLUtils {

    /**
     * 功能描述：格式化SQL，去除\t、\r、\n
     *
     * @param queryString SQL字符串
     * @return 格式化后的字符串
     */
    public static String formatSQL(final String queryString) {
        if (StringUtils.isBlank(queryString)) {
            return null;
        }
        // 是否引号结束
        boolean isQuoteEnd = true;
        // 上一个字符是否为空格
        boolean lastBlank = false;
        StringBuilder queryStringBuffer = new StringBuilder();
        for (int index = 0; index < queryString.length(); index++) {
            char c = queryString.charAt(index);
            // 处理\t、\r、\n
            boolean handleState = c == 10 || c == 9 || c == 13;
            if ((handleState || c == 32) && isQuoteEnd && lastBlank) {
                continue;
            }
            if (handleState && isQuoteEnd) {
                c = 32;
            }
            lastBlank = c == 32;
            queryStringBuffer.append(c);
            // 单引号是否结束标志，单引号中的内容不做任何处理
            if (c == 39) {
                isQuoteEnd = !isQuoteEnd;
            }
        }
        return queryStringBuffer.toString();
    }

    /**
     * 功能描述：解析SQL的参数
     *
     * @param sqlString 语句
     * @return 参数数组
     */
    public static List<String> parseSQLParams(final String sqlString) {
        List<String> params = new ArrayList<>(10);
        // 是否在单引号内
        boolean inQuote = false;
        for (int indx = 0; indx < sqlString.length(); indx++) {
            char c = sqlString.charAt(indx);
            // 单引号判断
            if (inQuote) {
                if ('\'' == c) {
                    inQuote = false;
                }
            } else if ('\'' == c) {
                inQuote = true;
            }
            // 上一个字符
            char lastChar = indx > 1 ? sqlString.charAt(indx - 1) : ' ';
            // 如果不在单引号内并且以:开始，表示变量开始
            if (!inQuote && c == ':' && lastChar != '\\') {
                // 变量右侧位置
                int right = StringHelper.firstIndexOfChar(sqlString, ParserHelper.HQL_SEPARATORS_BITSET, indx + 1);
                int chopLocation = right < 0 ? sqlString.length() : right;
                // 参数名
                String param = sqlString.substring(indx + 1, chopLocation);
                if (StringHelper.isEmpty(param)) {
                    throw new QueryException("Space is not allowed after parameter prefix ':' [" + sqlString + "]");
                }
                params.add(param);
                indx = chopLocation - 1;
            }
        }
        return params;
    }

    /**
     * 功能描述：clob转字符串
     *
     * @param clob clob参数
     * @return 字符串
     * @date 2020-11-03
     */
    public static String clobToString(Clob clob) throws Exception {
        StringBuilder sb = new StringBuilder();
        try (Reader is = clob.getCharacterStream(); BufferedReader br = new BufferedReader(is)) {
            // 得到流
            String s = br.readLine();
            // 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
            while (s != null) {
                sb.append(s);
                s = br.readLine();
                if (s != null) {
                    sb.append("\n");
                }
            }
            return sb.toString();
        }
    }

}
